insert overwrite table jms_dm.dm_tab_business_monitor_contrast_dt partition (dt)
select statistic_date, statistic_hour, statistic_format_date, count(1) scan_cnt, dt
from (select date(replace(get_json_object(log, '$.scanTime'), 'T', ' ')) statistic_date,
             hour(replace(get_json_object(log, '$.scanTime'), 'T', ' ')) statistic_hour,
             date_format(replace(get_json_object(log, '$.scanTime'), 'T', ' '),
                         'yyyy-MM-dd HH:00:00')                          statistic_format_date,
             date(replace(get_json_object(log, '$.scanTime'), 'T', ' ')) dt,
             get_json_object(log, '$.oracleId')                          RECORDID
      from jms_ods.tab_barscan_business_realtime_flink
      where dt >= date_add('{{ execution_date | cst_ds }}', -5) and dt <='{{ execution_date | cst_ds }}'
        and date(replace(get_json_object(log, '$.scanTime'), 'T', ' ')) >= date_add('{{ execution_date | cst_ds }}', -5)
        and date(replace(get_json_object(log, '$.scanTime'), 'T', ' ')) < date_add('{{ execution_date | cst_ds }}', 1)
      group by get_json_object(log, '$.oracleId'),
               date(replace(get_json_object(log, '$.scanTime'), 'T', ' ')),
               hour(replace(get_json_object(log, '$.scanTime'), 'T', ' ')),
               date_format(replace(get_json_object(log, '$.scanTime'), 'T', ' '), 'yyyy-MM-dd HH:00:00')) detail
group by statistic_date, statistic_hour, statistic_format_date, dt
    distribute by dt, 1
;